﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Data.Common;

namespace hoemwork
{
    public class Enroll
    {
        //注册/登录
        //内容：
        //发布/修改
        //单页呈现
        //列表页呈现（包括：过滤/分页）
        //批量标记Message为已读
        //……
        public static void EnrollFunction()
        {
            //用ADO.NET实现以下功能的数据库操作：
            //注册 / 登录
            string name = Console.ReadLine();
            string password = Console.ReadLine();

            ///1链接数据库connection
            string connectionString =
                @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";                                        //链接数据库
            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            {
                connection.Open();                                                //链接打开
                IDbCommand command = new SqlCommand();                            //声明命令对象
                command.Connection = connection;                                  //命令的链接 为链接
                                                                                  //  模拟登陆1用户名是否存在2密码是否正确
                command.CommandText =                                              //命令的文本形式
                    $"SELECT [Password] FROM [User]" +
                    $" WHERE[UserName] = '{name}' ";
                object dbpassword = command.ExecuteScalar()?.ToString();           // 取出命令的返回值
                if (dbpassword == null)                                            //判断密码是否为空
                {
                    Console.WriteLine("用户名不存在");
                    return;
                }
                if (dbpassword.ToString() != password)                              //判断命令的返回值是否和数据库中的密码一样    
                {
                    Console.WriteLine("*用户名或密码错误");
                    return;
                }
            }



        }

        public static void Commint()
        {
            Console.WriteLine("发布：请输入内容");
            string content = Console.ReadLine();
            string connectionString =
             @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";
            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            {
                connection.Open();                                                //链接打开
                IDbCommand command = new SqlCommand();                            //声明命令对象
                command.Connection = connection;                                  //命令的链接 为链接
                                                                                  //// 内容：发布
                command.CommandText =
                    "SELECT ID FROM [User] WHERE [UserName]=N'FG'";
                object UId = command.ExecuteScalar();
                command.CommandText =
                    $"INSERT Problem(NeedRemoteHelp,Content,UserId )" +
                    $" VALUES(1,N'{content}',{UId})";
                command.ExecuteNonQuery();
            }
        }
        public static void Update()
        {

            Console.WriteLine("修改：请输入需要修改的列名和数值 以及约束条件");
            string ucolumn = Console.ReadLine();
            string unumber = Console.ReadLine();
            string wcolumn = Console.ReadLine();
            string wnumber = Console.ReadLine();
            string connectionString =
             @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";
            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            {
                connection.Open();                                                //链接打开
                IDbCommand command = new SqlCommand();                            //声明命令对象
                command.Connection = connection;                                  //命令的链接 为链接
                command.CommandText = $"UPDATE Problem SET " +
                    $"{ucolumn}='{unumber}' WHERE {wcolumn} ='{wnumber}' ";
                command.ExecuteNonQuery();
            }
        }
        public static void MarkMessage()
        {
            string HasReadNum = Console.ReadLine();
            string connectionString =
             @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";

            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            {
                connection.Open();                                                //链接打开
                IDbCommand command = new SqlCommand();                            //声明命令对象
                command.Connection = connection;                                  //命令的链接 为链接
                                                                                  // 内容：修改
                command.CommandText =
                    $"UPDATE Message SET HasRead=1 WHERE ID <= {HasReadNum}";
                command.ExecuteNonQuery();
            }
        }
        public static void GetOnePage()
        {

            string OnePage = Console.ReadLine();
            string connectionString =
             @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";

            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            {
                connection.Open();                                                //链接打开
                IDbCommand command = new SqlCommand();                            //声明命令对象
                command.Connection = connection;                                  //命令的链接 为链接

                command.CommandText = $"SELECT {OnePage} FROM Problem";
                object result = command.ExecuteScalar();
                Console.WriteLine(result);
            }
        }
        public static void Pageing()
        {
            Console.WriteLine("请输入分页条件");
            string page = Console.ReadLine();
            string offset = Console.ReadLine();
            string FetchNext = Console.ReadLine();
            string connectionString =
             @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";

            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            {
                connection.Open();                                                //链接打开
                IDbCommand command = new SqlCommand();                            //声明命令对象
                command.Connection = connection;                                  //命令的链接 为链接

                command.CommandText =
                    $"SELECT * FROM Problem  ORDER BY {page} " +
                    $"OFFSET {offset} ROWS" +
                    $" FETCH NEXT {FetchNext} ROWS ONLY ";
                IDataReader Preader = command.ExecuteReader();
                Preader.Read();
                while (Preader.Read())
                {
                    for (int i = 0; i < Preader.FieldCount; i++)
                    {
                        Console.Write(Preader[i] + "  ");
                    }
                    Console.WriteLine();
                }

            }
        }
        public static void MoveOut()
        {
            Console.WriteLine("请输入过滤条件");
            string moveout = Console.ReadLine();
            string number = Console.ReadLine();
            string connectionString =
             @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";

            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            {
                connection.Open();                                                //链接打开
                IDbCommand command = new SqlCommand();                            //声明命令对象
                command.Connection = connection;                                  //命令的链接 为链接
                command.CommandText =
                    $"SELECT * FROM Problem WHERE {moveout}='{number}'";
                IDataReader reader = command.ExecuteReader();
                reader.Read();
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Console.Write(reader[i] + "  ");
                    }
                    Console.WriteLine();
                }

            }
        }
        //封装过后的ExecuteNonQuery() / ExecuteScalar() / ExecuteReader()方法
        //可以在一个连接中完成若干操作，比如：标记勾选的Message为已读
        //事务的支持




        public static void BmoneyTransaction()
        {
           


            //用“参数化查询”改写之前拼接的SQL代码
            //用事务完成帮帮币的交易
            DbHelper helper = new DbHelper();
            using (IDbConnection connection = helper.GetNewConnection())//使用using自动关闭链接
            {
                connection.Open();
                using (IDbTransaction transaction = connection.BeginTransaction())  //connection.BeginTransaction方法返回IDBTracsaction对象
                {
                    try
                    {
                        IDbCommand command = new SqlCommand();//声明命令的对象
                        command.Connection = connection;
                        command.Transaction = transaction;//事务对象赋值给命令对象的事务属性
                        command.CommandText = "UPDATE BangMoney SET Balance-=5 WHERE [Name]=N'LS'";//使用sql更改语句
                        command.ExecuteNonQuery();//执行语

                        command.CommandText = "UPDATE BangMoney SET Balance-=5 WHERE [Name]=N'LS'";
                        command.ExecuteNonQuery();

                        transaction.Commit(); //事务的提交
                    }
                    catch (Exception)
                    {
                        transaction.Rollback(); // 事务回滚
                        //注意不能对异常“不予处理”，直接吞了
                        throw;
                    }
                }



            }



        }
    }
}
